/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package com.univcasa.managing.impl;

import com.univcasa.db.model.CompteEtudiant;
import com.univcasa.managing.ApogeeStudentsManager;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.PreparedStatement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import org.apache.log4j.Logger;

/**
 *
 * @author issam
 */
public class ApogeeStudentsManagerImpl implements ApogeeStudentsManager{

    private static Logger log = Logger.getLogger(ApogeeStudentsManagerImpl.class);
    private Connection con;

    public ApogeeStudentsManagerImpl() {
        try{
            Class.forName( ApogeeDBConfig.APOGG_DB_DRIVER );
        }catch (ClassNotFoundException e){
            e.printStackTrace();
        }

    }

    public CompteEtudiant getStdCompteByCrieria(HashMap criteres) {
        List lista = getStdsFromApogeeByCriteria(criteres, 1);
        CompteEtudiant etudiant = new CompteEtudiant();
        for(Iterator itr = lista.iterator() ; itr.hasNext();){
            etudiant = (CompteEtudiant) itr.next();
        }
        return etudiant;
    }

    public List getStdsFromApogeeByCriteria(HashMap criteres){

        return getStdsFromApogeeByCriteria(criteres , 0) ;
    }

    public List getStdsFromApogeeByCriteria(HashMap criteres, int numRows){

        String annee = (String) criteres.get("annee");
        String etab = (String) criteres.get("etab");
        String cne = (String) criteres.get("cne");
        String apogee = (String) criteres.get("apogee");
        String nom = (String) criteres.get("nom");
        String prenom = (String) criteres.get("prenom");
        String cin = (String) criteres.get("cin");

        annee = (annee==null ||"".equals(annee) )?"%":annee;
        etab = (etab==null||"".equals(etab))?"%":etab;
        cne = (cne==null ||"".equals(cne) )?"%":cne;
        apogee = (apogee==null || "".equals(apogee))?"%":apogee;
        nom= nom==null||nom.equals("")?"%":nom;
        prenom= prenom==null||prenom.equals("")?"%":prenom;
        cin= cin==null||cin.equals("")?"%":cin;

            try {
            con = DriverManager.getConnection( ApogeeDBConfig.APOGG_DB_URL, ApogeeDBConfig.APOGG_DB_USER, ApogeeDBConfig.APOGG_DB_PASS);

            String sql = "select distinct " +
                    "cod_etu apogee," +
                    "lib_nom_pat_ind nom," +
                    "lib_pr1_ind prenom," +
                    "cin_ind cin," +
                    "cod_sex_etu sexe," +
                    "cod_nne_ind cne," +
                    "date_nai_ind birth," +
                    "lib_ad1||lib_ad2||lib_ad3 adresse," +
                    "cod_cmp etab," +
                    "cod_dip diplome," +
                    "cod_etp etape, " +
                    "ins_adm_etp.cod_anu " +
                    "from individu,ins_adm_etp,adresse " +
                    "where individu.cod_ind=ins_adm_etp.cod_ind " +
                    "and individu.cod_ind=adresse.cod_ind " +
                    "and eta_iae='E' " +
                    "and tem_iae_prm='O' " +
                    "and ins_adm_etp.cod_anu like ? " +
                    "and cod_cmp like ? " +
                    "and cod_etu like ? " +
                    "and cod_nne_ind like ? " +
                    "Order By ins_adm_etp.cod_anu desc" ;

            PreparedStatement stmt = con.prepareStatement(sql);
            stmt.setString(1, annee);
            stmt.setString(2, etab);
            stmt.setString(3,  apogee )  ;
            stmt.setString(4,  cne )  ;
            stmt.setMaxRows(numRows);
            //stmt.setMaxRows(500);

            ResultSet res = stmt.executeQuery();
            List resultats = new ArrayList();

            while(res.next()){
                CompteEtudiant etudiant = new CompteEtudiant();

                etudiant.setApogee( res.getString( "apogee" ) );
                etudiant.setCin( res.getString("cin" ) );
                etudiant.setCne( res.getString( "cne" ) );
                etudiant.setNom( res.getString("nom" ) );
                etudiant.setPrenom( res.getString( "prenom" ) );
                etudiant.setEtab( res.getString( "etab" ) );
                etudiant.setEtape( res.getString( "etape" ) );
                etudiant.setDiplome( res.getString( "diplome" ) );
                etudiant.setAdresse( res.getString( "adresse" ) );
                etudiant.setPassword( res.getString( "apogee" ) );
                etudiant.setNaissance( res.getDate( "birth" ) );
                etudiant.setSexe( "M".equals( res.getString("sexe") )?"M.":"Mlle" );

                resultats.add( etudiant );
            }
            stmt.close();
            con.close();
            return resultats;

        }catch (SQLException ex){
            log.info("ERROR SQLException",ex);
            return null;
        }
    }

    public List searchStdsFromApogeeByCriteria(HashMap criteres) {

        String cne = (String) criteres.get("cne");
        String apogee = (String) criteres.get("apogee");
        String nom = (String) criteres.get("nom");
        String prenom = (String) criteres.get("prenom");
        String cin = (String) criteres.get("cin");

        cne = (cne==null ||"".equals(cne) )?"%":cne;
        apogee = (apogee==null || "".equals(apogee))?"%":apogee;
        nom= nom==null||nom.equals("")?"%":nom;
        prenom= prenom==null||prenom.equals("")?"%":prenom;
        cin= cin==null||cin.equals("")?"%":cin;


            try {
            con = DriverManager.getConnection( ApogeeDBConfig.APOGG_DB_URL, ApogeeDBConfig.APOGG_DB_USER, ApogeeDBConfig.APOGG_DB_PASS);

            String sql = "select distinct " +
                    "cod_etu apogee," +
                    "lib_nom_pat_ind nom," +
                    "lib_pr1_ind prenom," +
                    "cin_ind cin," +
                    "cod_sex_etu sexe," +
                    "cod_nne_ind cne," +
                    "date_nai_ind birth," +
                    "lib_ad1||lib_ad2||lib_ad3 adresse " +
                    "from individu,adresse " +
                    "where individu.cod_ind=adresse.cod_ind " +
                    "and (cod_etu like ? ) " +
                    "and (cod_nne_ind like ? ) " +
                    "and (lib_nom_pat_ind like ? or lib_nom_pat_ind is null) " +
                    "and (lib_pr1_ind like ? or lib_pr1_ind is null) " ;
            //suppr'ession du critere CIN depuis recherche car renvoi bcp de resultat qd nulle
            //et ke seul cin est critere choisie
            //        "and (cin_ind like ? or cin_ind is null) " ;

            PreparedStatement stmt = con.prepareStatement(sql);
            stmt.setString(1,  apogee )  ;
            stmt.setString(2,  cne )  ;
            stmt.setString(3, nom  )  ;
            stmt.setString(4,  prenom )  ;
            //stmt.setString(5, cin  )  ;
            stmt.setMaxRows(0);
            //stmt.setMaxRows(500);

            ResultSet res = stmt.executeQuery();
            List resultats = new ArrayList();

            while(res.next()){
                CompteEtudiant etudiant = new CompteEtudiant();

                etudiant.setApogee( res.getString( "apogee" ) );
                etudiant.setCin( res.getString("cin" ) );
                etudiant.setCne( res.getString( "cne" ) );
                etudiant.setNom( res.getString("nom" ) );
                etudiant.setPrenom( res.getString( "prenom" ) );
                etudiant.setAdresse( res.getString( "adresse" ) );
                etudiant.setNaissance( res.getDate( "birth" ) );
                etudiant.setSexe( "M".equals( res.getString("sexe") )?"M.":"Mlle" );

                resultats.add( etudiant );
            }
            stmt.close();
            con.close();
            return resultats;

        }catch (SQLException ex){
            log.info("ERROR SQLException",ex);
            return null;
        }
    }
}